const SqliteUtil = appRequire('modules/SqliteUtil');
const PageResultVo = appRequire('vo/PageResultVo.js');
class SysUserRoleSqliteDao {
  constructor() {

  }
  static async create() {
    let db = await SqliteUtil.open(appConfig.dbPath);
    await db.run(`CREATE TABLE "sys_user_role" (
      "id" VARCHAR(64) NOT NULL,
      "user_id" VARCHAR(64)  NOT NULL DEFAULT '',
      "role_id" VARCHAR(64)  NOT NULL DEFAULT '',
      PRIMARY KEY ("id")
    );`);
    await db.close();
  }
  static async saveOne(user_role) {
    let db = await SqliteUtil.open(appConfig.dbPath);
    try {
      await db.run(`insert into sys_user_role
          (id,user_id,role_id) 
          values(?,?,?)`,
        [user_role.id, user_role.userId,user_role.roleId]);
    } catch (error) {
      throw new Error(error);
    } finally {
      await db.close();
    }
  }
  static async saveMany(user_roles) {
    let db = await SqliteUtil.open(appConfig.dbPath);
    try {
      await db.run('BEGIN TRANSACTION;');
      for(let i = 0;i<user_roles.length;i++){
        let user_role = user_roles[i];
        await db.run(`insert into sys_user_role
          (id,user_id,role_id) 
          values(?,?,?)`,
        [user_role.id, user_role.userId,user_role.roleId]);
      }
      await db.run('COMMIT TRANSACTION;');
    } catch (error) {
      await db.run('ROLLBACK;');
      throw new Error(error);
    } finally {
      await db.close();
    }
  }
  static async deleteManyByUserId(ids) {
    let db = await SqliteUtil.open(appConfig.dbPath);
    try {
      console.log(ids);
      let sqlWhere = 'where user_id in (';
      for(let i = 0;i<ids.length;i++){
        if(i!=ids.length-1){
          sqlWhere+=`'${ids[i]}',`
        }else{
          sqlWhere+=`'${ids[i]}')`
        }
      }
      let sql = `delete from sys_user_role ${sqlWhere}`;
      console.log(sql);
      await db.run(sql);
    } catch (error) {
      throw new Error(error);
    } finally {
      await db.close();
    }
  }
  static async updateOne(user_role) {
    let db = await SqliteUtil.open(appConfig.dbPath);
    try {
      await db.run(`update sys_user_role 
        set user_id=?,role_id=? where id = ?`,
      [user_role.userId,user_role.roleId,user_role.id]);
    } catch (error) {
      throw new Error(error);
    } finally {
      await db.close();
    }
  }
  static async deleteById(id) {
    let db = await SqliteUtil.open(appConfig.dbPath);
    try {
      await db.run(`delete from sys_user_role where id = ?`,
      [id]);
    } catch (error) {
      throw new Error(error);
    } finally {
      await db.close();
    }
  }
  static async findOne(id) {
    let db = await SqliteUtil.open(appConfig.dbPath);
    try {
      let data = await db.get(`select id,user_id as userId,role_id as roleId from sys_user_role where id=?`,[id]);
      return data;
    } catch (error) {
      throw new Error(error);
    } finally {
      await db.close();
    }
  }
  static async findById(id) {
    let db = await SqliteUtil.open(appConfig.dbPath);
    try {
      let data = await db.get(`select * from sys_user_role where id=?`,[id]);
      return data;
    } catch (error) {
      throw new Error(error);
    } finally {
      await db.close();
    }
  }
  static async findAll() {
    let db = await SqliteUtil.open(appConfig.dbPath);
    try {
      let data = await SqliteUtil.each(db,`select * from sys_user_role`);
      return data;
    } catch (error) {
      throw new Error(error);
    } finally {
      await db.close();
    }
  }
  static async findByPage(page) {
    let db = await SqliteUtil.open(appConfig.dbPath);
    try {
      let sqlWhere = '';
      let sqlParams = [(page.pageNum-1)*page.pageSize,page.pageSize];
      if(page.searchParams&&page.searchParams.userId){
        sqlWhere +='where user_id=?'
        sqlParams.unshift(page.searchParams.userId);
      }
      let result = await db.get(`select count(*) as totalNum from sys_user_role ${sqlWhere}`);
      let count = result.totalNum;
      let data = await SqliteUtil.each(db,`select id,user_id as userId,role_id as roleId from sys_user_role ${sqlWhere} limit ?,?`,sqlParams);
      //加了await 会等待data拼装完成
      return PageResultVo.success('success',count,data);
    } catch (error) {
      throw new Error(error);
    } finally {
      await db.close();
    }   
  }
}
module.exports = SysUserRoleSqliteDao;